#include <stdio.h>
#include <unistd.h>
#include <string.h>
#include <stdlib.h>
#include "mysql.h"

#define _HOST_ "127.0.0.1"
#define _USER_ "username"
#define _PASSWORD_ "password"
#define _DBNAME_ "scott"


// 测试连接
int main01_connect()
{
    // 1.  init  mysql

    MYSQL *mysql = mysql_init(NULL);

    if (mysql == NULL)
    {
        printf("init: %s\n", mysql_error(mysql));
        exit(1);
    }

    // 2. real_connect
    mysql = mysql_real_connect(mysql, _HOST_, _USER_, _PASSWORD_, _DBNAME_, 0, NULL, 0);

    if (mysql == NULL)
    {
        printf("connect : %s\n", mysql_error(mysql));
        exit(1);
    }



    printf("hello mysql !\n");

    // 3. close
    mysql_close(mysql);

    // gcc 01_mysql.c -I/usr/include/mysql  -L/lib/x86_64-linux-gnu  -lmysqlclient  -fexec-charset=utf-8

    // 只能root 用户登陆mysql 在cmd  解决
    // GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
    //  FLUSH PRIVILEGES;

    return 0;
}

// 测试插入  
int main02_insert()
{

    MYSQL *mysql = mysql_init(NULL);

    if (mysql == NULL)
    {
        printf("init: %s\n", mysql_error(mysql));
        exit(1);
    }

    // 2. real_connect
    mysql = mysql_real_connect(mysql, _HOST_, _USER_, _PASSWORD_, _DBNAME_, 0, NULL, 0);

    if (mysql == NULL)
    {
        printf("connect : %s\n", mysql_error(mysql));
        exit(1);
    }

    //设置字符集
    mysql_set_character_set(mysql,"utf8");

    char rSql[256] = {0};
    //问题：插入中文乱码，  
       // denganzhi@denganzhi-virtual-machine:~$ echo $LANG    输出:    en_US.UTF-8
       // 应该改成 中文版本 "zh_CN.UTF-8"


    strcpy(rSql, "insert into t1(name,birthday,salary,resume) value('xiaoze',now(),8000,'yuangong1')");

    if (mysql_query(mysql, rSql) != 0)
    {
        printf("insert  : %s\n", mysql_error(mysql));
        exit(1);
    }


    char rSql1[256] = {0};

    strcpy(rSql1, "select * from t1");

    if (mysql_query(mysql, rSql1) != 0)
    {
        printf("query  : %s\n", mysql_error(mysql));
        exit(1);
    }

    // 取回结果集
    MYSQL_RES * result = mysql_store_result(mysql);
    MYSQL_ROW  row; 
    int i=0; 
    if(result != NULL){
   // 打印结果集 ,循环取一行 
      while( (row= mysql_fetch_row(result) )  != NULL){
         for(int i=0;i<5;i++){  
              printf("%s \t", row[i]);
         }
         printf("\n");

      }
      // 释放结果集
      mysql_free_result(result);

    }



   // printf("hello mysql !\n");

    // 3. close
    mysql_close(mysql);

    return 0;
}


// 结果集中行数: 7
// 结果集中列数: 5
// +----------------+----------------+----------------+
// |             id |           name |       birthday |         salary |         resume |
// +----------------+----------------+----------------+
// |              1 |            ??? |     2025-01-20 |          43331 |             ?? |
// |              2 |             ?? |           NULL |           NULL |           NULL |
// |              3 |         xiaoze |           NULL |           8000 |             ?? |
// |              4 |         xiaoze |     2025-01-20 |           8000 |             ?? |
// |              5 |         xiaoze |     2025-01-20 |           8000 |            ??1 |
// |              6 |         xiaoze |     2025-01-20 |           8000 |             gs |
// |              7 |         xiaoze |     2025-01-20 |           8000 |      yuangong1 |



//显示结果集
int show_result(MYSQL_RES *res)
{

    int i, j;

    unsigned long row_nums = 0;
    unsigned int col_nums = 0;

    MYSQL_ROW row = NULL;

    MYSQL_FIELD *field = NULL;

    //1. 获取结果集中行数
    //my_ulonglong mysql_num_rows(MYSQL_RES *result) 
    row_nums = mysql_num_rows(res);
    printf("结果集中行数: %lu\n", row_nums);

    //2. 获取结果集中列数
    //unsigned int mysql_num_fields(MYSQL_RES *result)
    col_nums = mysql_num_fields(res);
    printf("结果集中列数: %u\n", col_nums);

    //3. 获取所有字段信息
    field = mysql_fetch_fields(res);

    printf("+----------------+----------------+----------------+\n|");
    //输出字段信息
    for (i = 0; i < col_nums; i++)
    {
        printf("%15s |", field[i].name); 
    }

    printf("\n+----------------+----------------+----------------+\n");

    //7. 输出所有的数据, 行 
    for (i = 0; i < row_nums; i++)    
    {
        putchar('|');
        //获取一行
        row = mysql_fetch_row(res); 
        if (NULL == row)
        {
            break; 
        }

        //循环输出每一列值， 列 
        for (j = 0; j < col_nums; j++)     
        {
            printf("%15s |", row[j] ? row[j] : "NULL"); 
        }

        //换行
        printf("\n");
    }
    printf("+----------------+----------------+----------------+\n");

    //8. 释放结果集
    //void mysql_free_result(MYSQL_RES *result)  
    mysql_free_result(res); 
    

    return 0;
}

#define SIZE 128

int main03_select(void)
{
    
    int ret = -1;
    MYSQL *mysql = NULL;
    MYSQL_RES *res = NULL;

    char buf[SIZE];

    //1. 初始化
    mysql = mysql_init(NULL);
    if (NULL == mysql)
    {
        printf("mysql_init failed..\n"); 
        goto err0;
    }
    printf("mysql_init ok...\n");

    //2. 连接到MySQL数据库
    mysql = mysql_real_connect(mysql, _HOST_, _USER_, _PASSWORD_, _DBNAME_, 0, NULL, 0);
    if (NULL == mysql)
    {
        printf("mysql_real_connect failed...\n");
        goto err1;
    }
    printf("连接到MySQL数据库ok....\n");


//设置字符集
    mysql_set_character_set(mysql,"utf8");

    //3. 执行select语句
    memset(buf, 0, SIZE);
    strcpy(buf, "select * from t1");
    ret = mysql_query(mysql, buf);
    if(0 != ret)
    {
        printf("mysql_query %s\n", mysql_error(mysql));
        goto err1;
    }
    else
    {
        //printf("%lu rows affected....\n", mysql_affected_rows(mysql)); 
    }

    //4. 获取结果集
    res = mysql_store_result(mysql);
    if (NULL == res)
    {
        printf("mysql_store_result failed...\n"); 
        goto err1;
    }

    //5. 显示结果集
    show_result(res);

    //6. 关闭连接
    mysql_close(mysql); 

    return 0;
err1:
    mysql_close(mysql); 
err0:
    return 1;
}

int main()
{
  //    main02_insert();


// 查询优化
     main03_select();

    return 0;
}

